from PySide6.QtWidgets import QDialog,QFileDialog,QMessageBox
from sqlalchemy.orm import Session
import os,openpyxl

from unicodedata import category

from settings.publicvar import engine
from views.book.download.bookdownloaddata import Ui_bookdownloaddata
from views.book.mode.book import Books, Author, Publisher, Category


class BookDownloadDataView(QDialog,Ui_bookdownloaddata):
    def __init__(self):
        super().__init__()
        self.setupUi(self)

        self.exceldownload.clicked.connect(self.exceldownloadFun)
        self.dbdownload.clicked.connect(self.dbdownloadFun)

    def exceldownloadFun(self):
        with Session(engine) as session,session.begin():
            books = session.query(Books).all()
            model_file_path = os.path.join('state', 'model', '图书模板.xlsx')
            data_all = openpyxl.load_workbook(model_file_path)
            data = data_all.active
            data.cell(2,14,'学校代码')
            data.cell(2,15,'学校名称')
            for i,item in enumerate(books):
                data.cell(row=i+3,column=1,value=item.id)
                data.cell(row=i+3,column=2,value=item.isbn)
                data.cell(i+3,3,item.title)
                if item.author is None:
                    data.cell(i+3,4,'')
                else:
                    data.cell(i + 3, 4, item.author.name)
                if item.publisher is None:
                    data.cell(i+3,5,'')
                else:
                    data.cell(i+3,5,item.publisher.name)
                data.cell(i+3,6,item.publish_date)
                data.cell(i+3,7,item.summary)
                data.cell(i+3,8,item.price)
                data.cell(i + 3,9, item.amount)
                if item.category is None:
                    data.cell(i+3,10,'')
                else:
                    data.cell(i+3,10,item.category.name)
                data.cell(i + 3,11, item.create_date)
                data.cell(i +3,12,item.update_date)
                data.cell(i+3,13,item.mark)
                if item.school is None:
                    data.cell(i+3,14,'')
                    data.cell(i+3,15,'')
                else:
                    data.cell(i+3,14,item.school.schoolnum)
                    data.cell(i+3,15,item.school.name)
            save_path,_ = QFileDialog.getSaveFileName(None,'下载',item.school.name +'图书明细下载.xlsx',f"*.xlsx")
            data_all.save(save_path)
            QMessageBox.information(None,'成功','下载成功！')
            self.close()


    def dbdownloadFun(self):
        with Session(engine) as session,session.begin():
            books = session.query(Books).all()
            authors = session.query(Author).all()
            publishers = session.query(Publisher).all()
            categorys = session.query(Category).all()
            if books:
                for item in books:
                    book_tem = {
                        'id': item.id,
                        'isbn': item.isbn,
                    'title': item.title,
                    'author_id': item.author_id,
                    'publisher_id': item.publisher_id,
                    'publish_date': item.publish_date,
                    'summary': item.summary,
                    'price': item.price,
                    'amount': item.amount,
                    'category_id': item.category_id,
                    'create_date': item.create_date,
                    'update_date': item.update_date,
                    'mark': item.mark
                    }

                    author_tem = {
                        'id': item.id,
                        'name':item.name
                    }